﻿Imports System.Data.SqlClient
'Imports System.Data
'Imports Microsoft.ReportingServices.Rendering.ExcelRenderer.Excel
'Imports Microsoft.Office.Interop.Excel

Public Class FrmSoTheoDoiBienDong
    Private conn As New SqlConnection
    Private cmd As New SqlCommand
    Private tbl As New DataSet
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbHuyen.DisplayMember = "T_huyen"
        CmbHuyen.ValueMember = "maH"
        CmbHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiTaiLieu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTaiLieu", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLoaiTaiLieu.DisplayMember = "TenLoaiTaiLieu"
        CmbLoaiTaiLieu.ValueMember = "maLoaiTaiLieuID"
        CmbLoaiTaiLieu.DataSource = tbl
        ob_cnn.Close()
    End Sub

    Private Sub CmbHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CmbHuyen.SelectedIndexChanged
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & CmbHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbXa.DisplayMember = "TenXa"
        CmbXa.ValueMember = "maxa"
        CmbXa.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDataGridViewSoTheoDoiBienDong()
        Dim TuNgay As String
        TuNgay = String.Format("{0:MM/dd/yyyy}", DateTimeTuNgay.Value)
        Dim DenNgay As String
        DenNgay = String.Format("{0:MM/dd/yyyy}", DateTimeDenNgay.Value)
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select bd.doituongid, bd.ChuSD1, ngaydoi= convert (char (10) , bd.ngayBD, 103), bd.GioBD, bd.toBD, bd.sothua1, bd.NoiDungBD,cho_o1 " &
                               " From  " &
                               "  BIENDONG bd, tenxa tx, tenhuyen th WHERE " &
                               " diachihuyenid= '" & CmbHuyen.SelectedValue.ToString & "' " &
                               " and diachixaid= '" & CmbXa.SelectedValue.ToString & "'  " &
                               " and bd.diachixaid = tx.maxa  and bd.diachihuyenid= th.mah  " &
                               " and datediff (day, ngayBD, '" & TuNgay & "') <= 0  " &
                               " and datediff (day, ngayBD, '" & DenNgay & "') >= 0  " &
                              " and bd.loaitailieuid= '" & CmbLoaiTaiLieu.SelectedValue.ToString & "' Order by ngaybd ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewBD.DataSource = tbl
        If (tbl.Rows.Count = 0) Then
            MessageBox.Show("không tìm thấy thông tin !")
        Else
           
        End If

        ob_cnn.Close()
    End Sub

    Private Sub FrmSoTheoDoiBienDong_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DateTimeTuNgay.Text = DateAdd(DateInterval.Year, -1, Today())
        DateTimeDenNgay.Text = Today()
        NapDonViHuyen()
        NapLoaiTaiLieu()
        'Tieude()
        DataGridViewBD.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewBD.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
    End Sub

    Private Sub Buttimkiem_Click(sender As Object, e As EventArgs) Handles Buttimkiem.Click
        NapDataGridViewSoTheoDoiBienDong()
    End Sub

    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub
 
    Private Sub DateTime(p1 As Object)
        Throw New NotImplementedException
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        LocDulieu()
        MessageBox.Show("Cập nhật thành công !", "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub
    Private Sub LocDulieu()
        Dim xoa As String
        xoa = "Delete from SOBIENDONG where maBDid <> 0 "
        Dim cnn As New SqlCommand(xoa, ob_cnn)
        ob_cnn.Open()
        cnn.ExecuteNonQuery()
        ob_cnn.Close()
        Dim sMaTD As String
        Dim sChusd As String
        Dim sNgayBD As String
        Dim sGioBD As String
        Dim sToBD As String
        Dim sthua1 As String
        Dim sNoidung As String
        Dim sCho_o1 As String
        Dim i As Integer
        For i = 0 To DataGridViewBD.Rows.Count - 1
            sMaTD = DataGridViewBD.Rows(i).Cells(0).Value()
            sChusd = DataGridViewBD.Rows(i).Cells(1).Value()
            sNgayBD = DataGridViewBD.Rows(i).Cells(2).Value()
            sGioBD = DataGridViewBD.Rows(i).Cells(3).Value()
            sToBD = DataGridViewBD.Rows(i).Cells(4).Value()
            sthua1 = DataGridViewBD.Rows(i).Cells(5).Value()
            sNoidung = DataGridViewBD.Rows(i).Cells(6).Value()
            sCho_o1 = DataGridViewBD.Rows(i).Cells(7).Value()
            Dim Sql As String = ""
            Sql = "Insert Into SOBIENDONG(Doituongid, chuSD1, ngayBD, giobd, toBD, sothua1, NoidungBD, cho_o1) " &
                "Values(N'" & sMaTD & "', N'" & RTrim(sMaTD) + " " + Trim(sChusd) + Chr(10) + LTrim(sCho_o1) & "', '" & sNgayBD.ToString + Chr(10) + sGioBD & "','" & sGioBD & "', " &
                " '" & sToBD & "', '" & sthua1 & "', N'" & LTrim(sNoidung) & "', N'" & sCho_o1 & "' )"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Next
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs)

        'Dim frm As New FrmSoBD
        'frm.ShowDialog()

    End Sub


    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        XuatExcelSTD()
       
    End Sub

    Private Sub DataGridViewBD_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewBD.RowPostPaint
        ' xuat so thu tu tu dong cho datagridview
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewBD.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewBD.RowHeadersWidth < Size.Width + 20 Then DataGridViewBD.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))

    End Sub

    Private Sub DateTimeTuNgay_ValueChanged(sender As Object, e As EventArgs) Handles DateTimeTuNgay.ValueChanged

    End Sub

    Private Sub Label1_Click(sender As Object, e As EventArgs) Handles Label1.Click

    End Sub
End Class